﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SQLite;
using System.Data;
using PDAMain.Interface;
using PDAMain.Entity;

namespace PDAMain.DAL.Dao
{
    class ReadingAmmeterDao : BaseDao
    {
        private string mprId = "";
        public string MprId
        {
            get { return mprId; }
            set { mprId = value; }
        }
        private string am_Meter_Id = "";
        public string Am_Meter_Id
        {
            get { return am_Meter_Id; }
            set { am_Meter_Id = value; }
        }
        /// <summary>
        /// 获取待抄表的记录
        /// </summary>
        /// <returns></returns>
        public DataSet list()
        {
            //尝试下载最新数据
            this.download();
            DataSet ammeterListDt = new DataSet();
            string strsql = " SELECT MPRID,EM_METER_ID,READMONTH,PRE_DEGREE,READDEGREE,DISTRICT_ID,"
                        + "BUILDING_ID,APARMENT_ID,BARCODE FROM ELECTRICITYMETER WHERE READDEGREE='0' and  PLAN_ID =" + Identity.PlanId;
            ammeterListDt = base.executeDataSet(strsql);
            return ammeterListDt;
        }
        /// <summary>
        /// 获取抄表计划的记录
        /// </summary>
        /// <returns></returns>
        public DataSet listPlan()
        {
            //尝试下载最新数据
            this.download();
            DataSet watermeterListDt = new DataSet();
            //string strsql = " SELECT planId,planName,month,meterCount  FROM WATERMETERPLAN  ";
            string strsql = "SELECT a.[planId],a.[planName],a.[month],count(b.[plan_id]) as meterCount FROM AMMETERPLAN a "
                             + "left join ELECTRICITYMETER b on a.[planId] = b.[PLAN_ID] where b.[readdegree] = 0  group by  a.[planId],a.[planName],a.[month],a.[meterCount]";
            watermeterListDt = base.executeDataSet(strsql);
            return watermeterListDt;
        }
        /// <summary>
        /// 获取待处理工单的数量
        /// </summary>
        /// <returns></returns>
        public string dataNum()
        {
            this.download();
            string num = "0";
            string str_Sql = "select count(*) from ELECTRICITYMETER  WHERE READDEGREE='0'";
            num = base.executeDataSet(str_Sql).Tables[0].Rows[0][0].ToString();
            if (Convert.ToInt32(num) > 99) num = "99+";
            return num;

        }
        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="ammeterID">电表ID</param>
        /// <returns></returns>
        public DataSet getDetail(string MprId)
        {
            //尝试下载最新数据
            this.download();

            DataSet ammeterrDetailDt = new DataSet();
            string strsql = " SELECT MPRId,EM_METER_ID,READMONTH,PRE_DEGREE,READDEGREE,DISTRICT_ID,"
                            + "BUILDING_ID,APARMENT_ID, BARCODE FROM ELECTRICITYMETER WHERE MPRId='" + MprId + "'";
            ammeterrDetailDt = base.executeDataSet(strsql);
            return ammeterrDetailDt;
        }

        /// <summary>
        /// 获取未抄表的记录
        /// </summary>
        /// <returns></returns>
        private DataSet getWaterDataSet()
        {
            DataSet ds = new DataSet();
            //if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            if (NetWorkState.checkWifiState())
            {
                //鏈接webService接口
                pdaWebReference.PdaService pda = base.getPdaWebService();
                string result = pda.listAmmeterReadingWorkOrder(Identity.dutyPerson);
                if (result != "")
                {
                    return base.convertToDataSet(result);
                }
            }
            return ds;
        }

        public bool deal(string mprid, string Degree, string CompletionDt, string UpdateBy)
        {
            //此處處理狀態需同時在本地數據庫和Web端更新狀態
            bool result = false;

            if (this.updateAMStatus(mprid, Degree, CompletionDt, UpdateBy))
            {
                //已通过WebService更新服务器状态，删除本地数据
                result = deleteData(mprid);
            }/*
            else
            {
                string strsql = "UPDATE ELECTRICITYMETER SET READDEGREE='" + Degree + "',READINGTIME='" + CompletionDt + "' WHERE MPRId='" + mprid + "'";

                return base.executeNonQuery(strsql);
            }*/
            return result;
        }

        public bool insertLacolTable(string mprid, string Degree, string CompletionDt, string UpdateBy)
        {
            bool result = false;
            string strsql = "UPDATE ELECTRICITYMETER SET READDEGREE='" + Degree + "',READINGTIME='" + CompletionDt + "' WHERE MPRId='" + mprid + "'";
            result = base.executeNonQuery(strsql);
            return result;
        }

        /// <summary>
        /// 通过WebService更新服务器状态
        /// </summary>
        /// <param name="repairId">工单</param>
        /// <param name="status">状态</param>
        /// <param name="comment">备注</param>
        /// <returns></returns>
        private bool updateAMStatus(string mprid, string Degree, string CompletionDt, string UpdateBy)
        {
            //if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
           if (NetWorkState.checkWifiState())
            {
                try
                {
                    //鏈接webService接口
                    pdaWebReference.PdaService pda = base.getPdaWebService();
                    return pda.updateAmmeterReadingWorkOrder(mprid, Degree, CompletionDt, UpdateBy);
                }
                catch (Exception ex)
                {
                    return false;
                }

            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 上传数据
        /// </summary>
        private void upload()
        {
            //if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            if (NetWorkState.checkWifiState())
            {
                //在wifi或gprs打开时都可以上传状态
                string sql_am = "select MPRId ,EM_METER_ID,READINGTIME,READDEGREE from ELECTRICITYMETER where READDEGREE!='0'";
                DataSet ds = base.executeDataSet(sql_am);
                if (ds != null && ds.Tables.Count > 0)
                {
                    DataTable dt = ds.Tables[0];
                    for (int index = 0; index < dt.Rows.Count; index++)
                    {
                        string mprid = dt.Rows[index]["MPRId"].ToString().Trim();
                        string meter_Id = dt.Rows[index]["EM_METER_ID"].ToString().Trim();
                        string completeDt = dt.Rows[index]["READINGTIME"].ToString().Trim();
                        string degree = dt.Rows[index]["READDEGREE"].ToString().Trim();
                        if (this.updateMeterDegree(mprid, degree, completeDt))
                        {
                            deleteData(mprid);
                            //base.executeNonQuery("delete from ELECTRICITYMETER where MPRId='" + mprid + "'");
                        }
                    }
                }
            }
            /*if (NetWorkState.checkWifiState())
            {
                //仅在wifi打开时上传图片
            }*/
        }
        /// <summary>
        /// 通过WebService更新服务器状态
        /// </summary>
        /// <param name="mprId">主键</param>
        /// <param name="completeDt">抄表时间</param>
        /// <param name="degree">表读数</param>
        /// <returns></returns>
        public bool updateMeterDegree(string mprId, string degree, string completeDt)
        {
            //if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            if (NetWorkState.checkWifiState())
            {
                try
                {
                    //鏈接webService接口
                    pdaWebReference.PdaService pda = base.getPdaWebService();
                    bool result = pda.updateAmmeterReadingWorkOrder(mprId, degree, completeDt, Identity.dutyPerson);
                    
                    return result;
                }
                catch (Exception ex)
                {
                    return false;
                }

            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 查询——查询符合条件的抄表记录
        /// </summary>
        /// <param name="watermeterID">表编号</param>
        /// <param name="buildingID">房号</param>
        /// <param name="timeStart">起始时间</param>
        /// <param name="timeEnd">结束时间</param>
        /// <returns></returns>
        public DataSet select(string ammeterID, string buildingID, string timeStart, string timeEnd)
        {
            //this.download();
            DataSet ammeterSelDt = new DataSet();
            pdaWebReference.PdaService pda = base.getPdaWebService();
            //string nowDate1 = DateTime.Now.AddDays(1).ToString();
            string strResult = pda.queryAmmeterReadingWorkOrder(ammeterID, buildingID, timeStart, timeEnd, DataHelper.UserDao.getLoginedUserAccount());
            if (strResult == string.Empty)
                return null;
            ammeterSelDt = base.convertToDataSet(strResult);
            return ammeterSelDt;
        }
        public void download()
        {
            if (NetWorkState.checkWifiState())
            {
                downloadData();
                downloadPlan();
            }
        }
        public void downloadData()
        {
            DataSet ds = this.getWaterDataSet();
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //驗證系統中是否存在該筆數據
                    string ammeterID = dt.Rows[i]["METERID"].ToString();//检查ammeterID是否有数据

                    if (this.verifyData(ammeterID))
                    {
                        this.insertData(dt.Rows[i]);
                    }
                }
            }
        }
        public void downloadPlan()
        {
            //DataSet ds = this.getWaterDataSet();
            DataSet dps = this.getWaterPlanSet();
            if (dps != null && dps.Tables.Count > 0)
            {
                DataTable dt = dps.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //驗證系統中是否存在該筆數據
                    //string mpId = dt.Rows[i]["mpId"].ToString();
                    string planId = dt.Rows[i]["planId"].ToString();
                    string month = dt.Rows[i]["month"].ToString();

                    if (this.verifyPlan(planId,month))
                    {
                        this.insertPlan(dt.Rows[i]);
                    }
                }
            }

        }
        public bool verifyPlan(string planId ,string month)
        {
            bool result = false;

            DataSet ds = base.executeDataSet("select 1 from  AMMETERPLAN where planId=" + planId + " and month=" +"'"+ month+"'");
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    result = true;
                return result;
            }
            catch
            {
                return result;
            }
            //return false;
        }
        public bool insertPlan(DataRow dr)
        {
            bool successFlag = false;
            string str_Sql = "INSERT INTO AMMETERPLAN(mpId,planId,month,planStatus,planName,meterCount) VALUES("
                + "'" + dr["mpId"] + "','" + dr["planId"] + "','"
                + dr["month"] + "','" + dr["planStatus"] + "','" + dr["planName"] + "','"
                + dr["meterCount"] + "')";
            try
            {
                successFlag = base.executeNonQuery(str_Sql);
                return successFlag;
            }
            catch (Exception ex)
            {
                return successFlag;
            }
            // return false;
        }
        /// <summary>
        /// 获取抄表计划信息
        /// </summary>
        /// <returns></returns>
        private DataSet getWaterPlanSet()
        {
            DataSet ds = new DataSet();
            //if (NetWorkState.checkWifiState() || NetWorkState.checkGprsState())
            if (NetWorkState.checkWifiState())
            {
                //鏈接webService接口
                pdaWebReference.PdaService pda = base.getPdaWebService();
                //string result = pda.listWatermeterReadingWorkOrder(Identity.dutyPerson);
                //string result = pda.listWaterMeterPlan(Identity.dutyPerson);
                string result = pda.listAmMeterMonthPlan(Identity.dutyPerson);
                if (result != "")
                {
                    return base.convertToDataSet(result);
                }
            }
            return ds;
        }
        /// <summary>
        /// 同步时判断本地数据库是否相同的记录，有则不同步
        /// </summary>
        /// <param name="activityUID">维修工单编号</param>
        /// <returns></returns>
        private bool verifyData(string ammeterID)
        {
            bool result = false;

            DataSet ds = base.executeDataSet("select 1 from  ELECTRICITYMETER where EM_METER_ID='" + ammeterID + "'");
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    result = true;
                return result;
            }
            catch
            {
                return result;
            }
        }
        /// <summary>
        /// 往本地库中插入数据
        /// </summary>
        /// <param name="dr"></param>
        private bool insertData(DataRow dr)
        {
            bool successFlag = false;
            string str_Sql = "INSERT INTO ELECTRICITYMETER(MPRId,EM_METER_ID,READMONTH,PRE_DEGREE,READDEGREE,DISTRICT_ID,BUILDING_ID,APARMENT_ID,READINGTIME,BARCODE,PLAN_ID) VALUES("
                + "'" + dr["mprId"] + "','" + dr["meterId"] + "','"
                + dr["MONTH"] + "','" + dr["lastDegree"] + "','"
                + dr["degree"] + "','" + dr["districtID"] + "','"
                + dr["buildingID"] + "','" + dr["aparmentID"] + "','" + dr["readingTime"]
                + "','" + dr["barcode"] + "','" + dr["planID"] + "')";
            try
            {
                successFlag = base.executeNonQuery(str_Sql);
                return successFlag;
            }
            catch (Exception ex)
            {
                return successFlag;
            }
        }
        public DataTable readedData()
        {
            string AmmeterSql = "select MPRId,EM_METER_ID,READDEGREE,READINGTIME from ELECTRICITYMETER where READDEGREE>0";
            DataTable amTbable = null;
            amTbable = base.executeDataSet(AmmeterSql).Tables[0];
            return amTbable;
        }
        public bool deleteData( string mprid) 
        {
            bool result = false;
            string sqlstr = "delete from ELECTRICITYMETER where MPRId='" + mprid + "'";
             result = base.executeNonQuery(sqlstr);
            return result;
        }

    }
}
